package com.ms.game.invitefriend.db;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.time.DateUtils;
import org.hibernate.LockOptions;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.ms.game.db.util.UpdateQueryBuilder;
import com.ms.player.Player;
import com.ms.player.PlayerIdentityType;
import com.xpec.c4.db.dao.PlayerDAO;
import com.xpec.c4.db.hibernate.manager.HibernateLogManager;
import com.xpec.c4.db.hibernate.manager.HibernateManager;
import com.xpec.c4.game.utility.GUID;
import com.xpec.c4.game.utility.GameUtility;

public class DistributorInviteCodeDAO {
	
	/**
	 * 用playerId 跟 identifyType 取得經銷商邀請碼
	 * @param playerId
	 * @param identifyType
	 * @return
	 */
	public static ArrayList<DistributorInviteCodesModel> getDistributorInviteCodeModels (String playerId, byte identifyType , int limitCount){
		
		if(identifyType == PlayerIdentityType.BOTH_DISTRIBUTOR_DOWNLINE){
			identifyType = PlayerIdentityType.DISTRIBUTOR;
		}
		
		String queryString = "From DistributorInviteCodesModel Where ";
		if(identifyType == PlayerIdentityType.DISTRIBUTOR){
			queryString += "distributorGuid = :GUID ";
		}else if(identifyType == PlayerIdentityType.SUB_DISTRIBUTOR){
			queryString += "subDistributorGuid = :GUID ";
		}
		queryString += "and identifyType = :TYPE and invalid = false and used = false order by createTime asc";
		
		Session session = HibernateManager.getSession();
		Query query = session.createQuery(queryString);
		query.setParameter("GUID", playerId);
		query.setParameter("TYPE", identifyType);
		query.setMaxResults(limitCount);
		ArrayList<DistributorInviteCodesModel> models = (ArrayList<DistributorInviteCodesModel>)query.list();
		HibernateManager.endSession();
		return models;
	}
	
	/**
	 * 新增經銷商
	 * @param player
	 * @param inviteCodeCount
	 * @param designatedLeveMoney
	 * @param levelUpMoney
	 * @return
	 */
	public static boolean InsertNewDistributor(Player player, int inviteCodeCount, int designatedLeveMoney, int levelUpMoney, boolean isEffective) {
		DistributorsModel model = new DistributorsModel();
		model.setGuid(player.getGuid().toString());
		model.setPlayerName(player.getNameWithNumber());
		model.setInviteCodeCount(inviteCodeCount);
		model.setDesignatedLeveMoney(designatedLeveMoney);
		model.setLevelUpMoney(levelUpMoney);
		model.setEffective(isEffective);
		
		Session session = HibernateManager.txBegin();
		session.save(model);
		return HibernateManager.txCommitFailRollbackClearSession();
	}
	
	public static boolean UpdateDistributorSettings(String guid, int inviteCodeCount, int designatedLeveMoney, int levelUpMoney, boolean isEffective) {
		Session session = HibernateManager.getSession();
		
		Transaction tx = session.beginTransaction();
		
		try{
			UpdateQueryBuilder builder = new UpdateQueryBuilder();
			builder.setSession(session);
			builder.setTableName("DistributorsModel");
			builder.addSetValue("inviteCodeCount", inviteCodeCount);
			builder.addSetValue("designatedLeveMoney", designatedLeveMoney);
			builder.addSetValue("levelUpMoney", levelUpMoney);
			builder.addSetValue("effective", isEffective);
			builder.setWhereKey("guid", guid);
			builder.executeQuery();
			
			session.clear();
			
			
			Query query = session.createQuery("From SubDistributorsModel Where distributorGuid = :GUID");
			query.setLockOptions(LockOptions.UPGRADE);
			query.setParameter("GUID", guid);
			ArrayList<SubDistributorsModel> subModels = (ArrayList<SubDistributorsModel>) query.list();
			
			session.clear();
			
			boolean change = false;
			for (SubDistributorsModel subModel : subModels) {
				if(subModel.getDesignatedLeveMoneyComm() > designatedLeveMoney){
					subModel.setDesignatedLeveMoneyComm(designatedLeveMoney);
					change =true;
				}
				
				if(subModel.getLevelUpMoneyComm() > levelUpMoney){
					subModel.setLevelUpMoneyComm(levelUpMoney);
					change =true;
				}
				
				if(subModel.getModifyDesignatedLeveMoneyComm() > designatedLeveMoney){
					subModel.setModifyDesignatedLeveMoneyComm(designatedLeveMoney);
					change =true;
				}
				
				if(subModel.getModifyLevelUpMoneyComm() > levelUpMoney){
					subModel.setModifyLevelUpMoneyComm(levelUpMoney);
					change =true;
				}
				if(change){
					session.update(subModel);
				}
			}
			tx.commit();
			
		}catch(Exception e){
			tx.rollback();
			return false;
		}

		
		HibernateManager.endSession();
		return true;
		
	}
		
	/**
	 * 用playerID 取得DistributorsModel
	 * @param playerId
	 * @return
	 */
	public static DistributorsModel getDistributorModelByGUID(String guid){
		Session session = HibernateManager.getSession();
		Query query = session.createQuery("From DistributorsModel where guid = :GUID");
		query.setParameter("GUID", guid);
		DistributorsModel model = (DistributorsModel) query.uniqueResult();
		HibernateManager.endSession();
		return model;
	}
	
	public static boolean updateUsedDistributorInviteCode(Player player, String inviteCode) {
		String guid = player.getGuid().toString();
		String name = player.getNameWithNumber();
		Session session = HibernateManager.txBegin();	
		
		Transaction tx = session.beginTransaction();
		try{		
			Query query = session.createQuery("From DistributorInviteCodesModel i where inviteCode = :CODE");
			// 執行select for update
			query.setLockOptions(LockOptions.UPGRADE);
			query.setParameter("CODE", inviteCode);
			// 送出這句
			DistributorInviteCodesModel inviteCodesModel = (DistributorInviteCodesModel)query.uniqueResult();
			
			session.clear();
			
			inviteCodesModel.setUsed(true);
			inviteCodesModel.setUseTime(new Date(player.getCreateTime()));
			if (inviteCodesModel.getIdentifyType() == PlayerIdentityType.DISTRIBUTOR) {
				inviteCodesModel.setSubDistributorGuid(guid);
				inviteCodesModel.setSubDistributorName(name);
				player.setPlayerIdentityType(PlayerIdentityType.SUB_DISTRIBUTOR);
				player.setUplineId(inviteCodesModel.getDistributorGuid());
			} else if (inviteCodesModel.getIdentifyType() == PlayerIdentityType.SUB_DISTRIBUTOR) {
				inviteCodesModel.setDownLineGuid(guid);
				inviteCodesModel.setDownLineName(name);
				player.setPlayerIdentityType(PlayerIdentityType.DOWNLINE);
			}
			
			session.update(inviteCodesModel);
			//結束
			tx.commit();	
			//如果是子經銷商，要把此人加到子經銷商的table中
		}catch(Exception e){
			//出錯直接回滾
			tx.rollback();
			HibernateManager.endSession();
			return false;
		}
		HibernateManager.endSession();
		return true;

	}	
	
	/**
	 * 用邀請碼 取得 經銷商邀請碼Model
	 * @param inviteCode
	 * @return
	 */
	public static DistributorInviteCodesModel getDistributorInviteCodesModel (String inviteCode){
		Session session = HibernateManager.getSession();
		Query query = session.createQuery("From DistributorInviteCodesModel where inviteCode = :CODE and invalid = false");
		query.setParameter("CODE", inviteCode);
		DistributorInviteCodesModel inviteCodesModel = (DistributorInviteCodesModel) query.uniqueResult();
		session.clear();
		HibernateManager.endSession();
		return inviteCodesModel;
	}
	
	
	/**
	 * 檢查邀請碼是否被擁有人顯示出來 
	 * 有才能用
	 * 不然不給用
	 * @param inviteCodesModel
	 * @return
	 */
	public static boolean isDistributorInviteCodeShowing(DistributorInviteCodesModel inviteCodesModel){
		
		if (inviteCodesModel.getIdentifyType() == PlayerIdentityType.NORMAL_PLAYER) {
			return false;
		}
		
		String ownerId = inviteCodesModel.getIdentifyType() == PlayerIdentityType.SUB_DISTRIBUTOR ? inviteCodesModel.getSubDistributorGuid() : inviteCodesModel.getDistributorGuid();
		ArrayList<DistributorInviteCodesModel> models = getDistributorInviteCodeModels(ownerId, inviteCodesModel.getIdentifyType(), 6);
		
		for (DistributorInviteCodesModel distributorInviteCodesModel : models) {
			if (distributorInviteCodesModel.getInviteCode().equals(inviteCodesModel.getInviteCode())) {
				return true;
			}
		}
		
		return false;
	}
	
	public static boolean setSubDistributor(Player subDistributor){
		String uplineId = subDistributor.getUplineId();
		Session session = HibernateManager.getSession();
		Query query = session.createQuery("Select new map(designatedLeveMoney as designatedLeveMoney, levelUpMoney as levelUpMoney) From DistributorsModel Where guid = :GUID");
		query.setParameter("GUID", uplineId);
		HashMap<String, Integer> result = (HashMap<String, Integer>) query.uniqueResult();
		HibernateManager.endSession();
		
		int designatedLeveMoney = result.get("designatedLeveMoney");
		int levelUpMoney = result.get("levelUpMoney");
		
		SubDistributorsModel subDistributorsModel = new SubDistributorsModel();
		subDistributorsModel.setDesignatedLeveMoneyComm(designatedLeveMoney);
		subDistributorsModel.setLevelUpMoneyComm(levelUpMoney);
		subDistributorsModel.setDistributorGuid(uplineId);
		subDistributorsModel.setLevelUpMoneyComm(-1);
		subDistributorsModel.setDesignatedLeveMoneyComm(-1);
		subDistributorsModel.setSubDistributorGuid(subDistributor.getGuid().toString());
		subDistributorsModel.setSubDistributorName(subDistributor.getNameWithNumber());
		
		session = HibernateManager.txBegin();
		session.save(subDistributorsModel);
		return HibernateManager.txCommitFailRollbackClearSession();
	}
	
	/**
	 * 用經銷商 playerId 取得所有子經銷商
	 * @param playerId
	 * @return
	 */
	public static ArrayList<SubDistributorsModel> getAllSubDistributorsByGUID(String guid){
		Session session = HibernateManager.getSession();
		Query query = session.createQuery("From SubDistributorsModel where distributorGuid = :GUID");
		query.setParameter("GUID", guid);
		ArrayList<SubDistributorsModel> models = (ArrayList<SubDistributorsModel>) query.list();
		HibernateManager.endSession();
		return models;
	}
	
	/**
	 * 取得總共頁數
	 * @return
	 */
	public static int getTotalSubDistributorsPage(String guid, int maxPerPage){
		String queryString= "select count(p) from SubDistributorsModel p where  distributorGuid = :GUID";
		
		Session session = HibernateManager.getSession();
		Query query = session.createQuery(queryString);
		query.setParameter("GUID", guid);
		long dataCount = (long) query.uniqueResult();
		HibernateManager.endSession();
		
		return GameUtility.calculatePageCount((int)dataCount, maxPerPage);
	}
	
	/**
	 * 取得子經銷商 by Page
	 * @param guid
	 * @param beginPos
	 * @param maxPerPage
	 * @return
	 */
	public static ArrayList<SubDistributorsModel> getSubDistributorsByPage(String guid, int beginPos, int maxPerPage) {
		String queryStr = "From SubDistributorsModel where distributorGuid = :GUID order by createTime asc";
		Session session = HibernateManager.getSession();
		Query query = session.createQuery(queryStr);
		query.setParameter("GUID", guid);
		query.setFirstResult(beginPos);
		query.setMaxResults(maxPerPage);
		ArrayList<SubDistributorsModel> subDistributorModels = (ArrayList<SubDistributorsModel>) query.list();
		HibernateManager.endSession();

		return subDistributorModels;
	}
	
	
	/**
	 * 
	 * @param identifyType
	 * @param guid
	 * 		     請求方的guid
	 * @param subDistributorGUID
	 * 		     如果自己是子經銷商，就帶自己的guid
	 * @param maxPerPage
	 * @return
	 */
	public static int getTotalDownlineCommInfoPage(int identifyType, String subDistributorGUID, int maxPerPage){
		Session session = HibernateManager.getSession();
		
		String queryString = "Select count(i) From DistributorInviteCodesModel i where subDistributorGuid = :SUBGUID and used = true ";
		if(identifyType == PlayerIdentityType.DISTRIBUTOR || identifyType == PlayerIdentityType.BOTH_DISTRIBUTOR_DOWNLINE) {
			//子經銷商+下線
		} else if (identifyType == PlayerIdentityType.SUB_DISTRIBUTOR) {
			//只要下線
			queryString += "and identifyType = 2";
		}
		
		queryString += "order by identifyType asc, useTime asc";
		
		Query query = session.createQuery(queryString);
		query.setParameter("SUBGUID", subDistributorGUID);
		long dataCount = (long) query.uniqueResult();
		HibernateManager.endSession();
		
		return GameUtility.calculatePageCount((int)dataCount, maxPerPage);
	}
	
	
	/**
	 * 
	 * @param guid
	 * 		     請求方的guid
	 * @param subDistributorGUID
	 * 		     如果自己是子經銷商，就帶自己的guid
	 * @param identifyType
	 * @param beginPos
	 * @param maxPerPage
	 * @return
	 */
	public static ArrayList<DistributorInviteCodesModel> getDownlineCommInfoByPage(String subDistributorGUID, int identifyType, int beginPos, int maxPerPage) {

		Session session = HibernateManager.getSession();
		
		String queryString = "Select i From DistributorInviteCodesModel i where subDistributorGuid = :SUBGUID and used = true ";
		if(identifyType == PlayerIdentityType.DISTRIBUTOR || identifyType == PlayerIdentityType.BOTH_DISTRIBUTOR_DOWNLINE) {
			//子經銷商+下線
		} else if (identifyType == PlayerIdentityType.SUB_DISTRIBUTOR) {
			//只要下線
			queryString += "and identifyType = 2 ";
		}
		queryString += "order by identifyType asc, useTime asc";
		
		Query query = session.createQuery(queryString);
		query.setParameter("SUBGUID", subDistributorGUID);
		query.setFirstResult(beginPos);
		query.setMaxResults(maxPerPage);
		ArrayList<DistributorInviteCodesModel> models = (ArrayList<DistributorInviteCodesModel>) query.list();
		HibernateManager.endSession();

		return models;
	}
	
	/**
	 * 取得抽傭總和
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static HashMap<String, InviteFriendComSumLogModel> getPlayerComSumsByGuidList(List<String> guids, String uplineId){	
		Session session = HibernateLogManager.getSession();
				
		Query query = session.createQuery("Select new map(sum(comValue) as sum, i as model) From InviteFriendComSumLogModel i where uplineId =:UPLINE and downLineGuid in (:GUIDS) group by downLineGuid");
		query.setParameter("UPLINE", uplineId);
		query.setParameterList("GUIDS", guids);
		ArrayList<HashMap<String, Object>> result = (ArrayList<HashMap<String, Object>>)query.list();
		HibernateLogManager.endSession();
		
		HashMap<String, InviteFriendComSumLogModel> resultMap = new HashMap<String, InviteFriendComSumLogModel>();
		for (HashMap<String, Object> hashMap : result) {
			InviteFriendComSumLogModel model = (InviteFriendComSumLogModel) hashMap.get("model"); 
			model.setComValue((BigDecimal)hashMap.get("sum"));
			resultMap.put(model.getDownLineGuid(), model);
		}
		
		for (String guid : guids) {
			if (!resultMap.containsKey(guid)) {
				// InviteFriendComSumLogModel 沒這個人的資料，那就要自己拼湊出來 sum = 0 的抽傭資訊
				InviteFriendComSumLogModel model = new InviteFriendComSumLogModel();
				model.setComValue(BigDecimal.ZERO);
				model.setDownLineGuid(guid);
				String playerName = PlayerDAO.getPlayerNameByGuid(GUID.parseUUID(guid));
				model.setDownLineName(playerName);
				resultMap.put(model.getDownLineGuid(), model);
			}
		}
		return resultMap;
	}
	
	/**
	 * 用playerId 取得 對應的子經銷商Model
	 * @param guid
	 * @return
	 */
	public static SubDistributorsModel getSubDistributorsModelByGuid(String guid){
		Session session = HibernateManager.getSession();
		Query query = session.createQuery("From SubDistributorsModel where subDistributorGuid = :GUID");
		query.setParameter("GUID", guid);
		SubDistributorsModel model = (SubDistributorsModel) query.uniqueResult();
		HibernateManager.endSession();
		return model;
	}
	
	
	/**
	 * 用playerId 跟 角色類型去撈取剩餘的的邀請碼數量
	 * @param guid
	 * @return
	 */
	public static int getLeftDistributorInviteCodeCount(String guid, byte identifyType){
		
		if(identifyType == PlayerIdentityType.BOTH_DISTRIBUTOR_DOWNLINE) {
			identifyType = PlayerIdentityType.DISTRIBUTOR;
		}
		
		String queryString = "Select count(s) From DistributorInviteCodesModel s Where ";
		
		if(identifyType == PlayerIdentityType.DISTRIBUTOR) {
			queryString += "distributorGuid = :GUID ";
		}else if(identifyType == PlayerIdentityType.SUB_DISTRIBUTOR){
			queryString += "subDistributorGuid = :GUID ";
		}else {
			return 0;
		}
		
		queryString += " and used = false and invalid = false and identifyType = :TYPE";
		
		Session session = HibernateManager.getSession();
		//首先檢查自己剩下的
		Query query = session.createQuery(queryString);
		query.setParameter("TYPE", identifyType);
		query.setParameter("GUID", guid);
		Long count = (Long)query.uniqueResult();
		HibernateManager.endSession();
		return count.intValue();
	}
	


	@SuppressWarnings("unchecked")
	public static boolean modifySubDistributorInviteCodeNum(SubDistributorsModel subDistributorsModel, int totalCount, Integer addCount) {	
		
		String distributorGuid = subDistributorsModel.getDistributorGuid() ;
		String subDistributorGuid = subDistributorsModel.getSubDistributorGuid();
		String subDistributorName = subDistributorsModel.getSubDistributorName();
		
		int totalInviteCodeNum = 0;
		Session session = HibernateManager.getSession();
		Transaction tx = session.beginTransaction();
		try {
			if (addCount > 0) {
				// 增加給子經銷商
				
				// 這句是要做lock
				SQLQuery sqlQuery = session.createSQLQuery("Select * From distributor_invite_codes Where distributorGuid = :DISGUID and used = false and invalid = false and identifyType = :TYPE order by createTime asc limit :LIMIT for update");
				sqlQuery.addEntity(DistributorInviteCodesModel.class);
				sqlQuery.setParameter("DISGUID", distributorGuid);
				sqlQuery.setParameter("LIMIT", addCount);
				sqlQuery.setParameter("TYPE", PlayerIdentityType.DISTRIBUTOR);
				ArrayList<DistributorInviteCodesModel> inviteCodesModels = (ArrayList<DistributorInviteCodesModel>) sqlQuery.list();

				session.clear();

				ArrayList<String> updateCodes = new ArrayList<String>();
				for (DistributorInviteCodesModel inviteCodesModel : inviteCodesModels) {
					updateCodes.add(inviteCodesModel.getInviteCode());
				}

				sqlQuery = session.createSQLQuery("Update distributor_invite_codes set subDistributorGuid = :SUBGIUD, subDistributorName =:SUBNAME, identifyType = :TYPE Where inviteCode in (:CODES)");
				sqlQuery.setParameter("SUBGIUD", subDistributorGuid);
				sqlQuery.setParameter("SUBNAME", subDistributorName);
				sqlQuery.setParameter("TYPE", PlayerIdentityType.SUB_DISTRIBUTOR);
				sqlQuery.setParameterList("CODES", updateCodes);
				sqlQuery.executeUpdate();
				tx.commit();
				session.clear();
				
				totalInviteCodeNum = totalCount + addCount;
			} else if (addCount < 0) {
				// 從子經銷商扣除
				addCount = Math.abs(addCount);
				
				SQLQuery sqlQuery = session.createSQLQuery("Select inviteCode From distributor_invite_codes Where subDistributorGuid = :SUBGUID and used = false and invalid = false and identifyType = :TYPE order by createTime desc limit :LIMIT for update");
				sqlQuery.setParameter("SUBGUID", subDistributorGuid);
				sqlQuery.setParameter("LIMIT", addCount);
				sqlQuery.setParameter("TYPE", PlayerIdentityType.SUB_DISTRIBUTOR);
				List<String> invalidInviteCodes = (List<String>) sqlQuery.list();

				session.clear();

				// 真正要拿走的數量
				addCount = invalidInviteCodes.size();

				if (addCount <= 0) {
					// 完全沒有7
					tx.commit();
					HibernateManager.endSession();
					return false;
				}
				
				sqlQuery = session.createSQLQuery("Update distributor_invite_codes set invalid = true , invalidTime = :TIME Where inviteCode in (:CODES)");
				sqlQuery.setParameter("TIME", new Date());
				sqlQuery.setParameterList("CODES", invalidInviteCodes);
				sqlQuery.executeUpdate();
				tx.commit();
				
				session.clear();
				if (!addInviteCodesToDistribuor(session, distributorGuid, addCount)) {
					HibernateManager.endSession();
					return false;
				}
		
				totalInviteCodeNum = totalCount - addCount;
			}
			
			tx = session.beginTransaction();

			subDistributorsModel.setInviteCodeNum(totalInviteCodeNum);
			if (totalInviteCodeNum <= 0) {
				subDistributorsModel.setAfterFirstSetting(false);
				subDistributorsModel.setModify(false);
				subDistributorsModel.setModifyTime(null);
			}
			
			session.update(subDistributorsModel);
			
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			HibernateManager.endSession();
			return false;
		}
		HibernateManager.endSession();
		return true;
	}
	
	/**
	 * @param subDistributorsModel
	 * @param modifyLevelUpMoneyComm
	 * @param modifyDesignatedLeveMoneyComm
	 * @return
	 */
	public static boolean modifySubDistributorCommPreSetting(SubDistributorsModel subDistributorsModel, int modifyLevelUpMoneyComm, int modifyDesignatedLeveMoneyComm) {
		Session session = HibernateManager.getSession();
		
		Transaction tx = session.beginTransaction();
		
		try{
			
			// 這個Select是要把這個row lock 起來，怕其他人搶來用
			String selectQuery = "From SubDistributorsModel Where subDistributorGuid = :SUBGUID";
			Query query = session.createQuery(selectQuery);
			query.setLockOptions(LockOptions.UPGRADE);
			query.setParameter("SUBGUID", subDistributorsModel.getSubDistributorGuid());
			SubDistributorsModel lockModel = (SubDistributorsModel) query.uniqueResult();
			
			session.clear();
			
			if(subDistributorsModel.getInviteCodeNum() > 0) {
				// 有設定邀請碼的狀況下
				boolean isAfterFirstSetting = subDistributorsModel.isAfterFirstSetting();
				if(isAfterFirstSetting){
					if(subDistributorsModel.getDesignatedLeveMoneyComm() == modifyDesignatedLeveMoneyComm && 
							subDistributorsModel.getLevelUpMoneyComm() == modifyLevelUpMoneyComm){
						// 調整成原本的樣子那就復原吧
						subDistributorsModel.setModifyLevelUpMoneyComm(0);
						subDistributorsModel.setModifyDesignatedLeveMoneyComm(0);
						subDistributorsModel.setModify(false);
						subDistributorsModel.setModifyTime(null);
					}else {
						// 第一次設定之後還要再設定，就要等到明天0點才生效
						subDistributorsModel.setModifyLevelUpMoneyComm(modifyLevelUpMoneyComm);
						subDistributorsModel.setModifyDesignatedLeveMoneyComm(modifyDesignatedLeveMoneyComm);
						subDistributorsModel.setModify(true);			
						Date date = new Date();
						date = DateUtils.setHours(date, 0);
						date = DateUtils.setMinutes(date, 0);
						date = DateUtils.setSeconds(date, 0);
						date = DateUtils.setMilliseconds(date, 0);
						date = DateUtils.addDays(date, 1);
						
						subDistributorsModel.setModifyTime(date);
					}
				} else {
					// 第一次設定，就直接上了
					subDistributorsModel.setLevelUpMoneyComm(modifyLevelUpMoneyComm);
					subDistributorsModel.setDesignatedLeveMoneyComm(modifyDesignatedLeveMoneyComm);
					subDistributorsModel.setAfterFirstSetting(true);
				}
			}else {
				// 邀請碼0等於重新開始
				subDistributorsModel.setAfterFirstSetting(false);
				subDistributorsModel.setDesignatedLeveMoneyComm(-1);
				subDistributorsModel.setLevelUpMoneyComm(-1);
			}
			
			session.update(subDistributorsModel);
			tx.commit();
		}catch(Exception e){
			tx.rollback();
			return false;
		}finally{
			HibernateManager.endSession();
		}
		return true;
	}
	
	
	public static boolean modifyReservedInviteCodesToSubDistributor(int addCount,String distributorGuid, String subDistributorGuid, String subDistributorName, Date modifyDate) {
		Session	session = HibernateManager.txBegin();
		String updateString = "";
		SQLQuery sqlQuery = null;
		if(addCount == 0 ){
			return true;
		} else if(addCount > 0){
			//占位置
			updateString = "Update distributor_invite_codes set candidate = :SUB,candidateName = :SUBNAME , retain = true, retainTime = :TIME Where distributorGuid =:DIS and retain = false and used = false and identifyType = 1 order by createTime asc limit :LIMIT ";
			sqlQuery = session.createSQLQuery(updateString);
			sqlQuery.setParameter("SUB", subDistributorGuid);
			sqlQuery.setParameter("SUBNAME", subDistributorName);
			sqlQuery.setParameter("DIS", distributorGuid);
			sqlQuery.setParameter("LIMIT", addCount);
			sqlQuery.setParameter("TIME", modifyDate);
		}else if (addCount <0){
			//要移除
			updateString = "Update distributor_invite_codes set candidate = null,candidateName = null , retain = false, retainTime = null Where candidate = :SUB and retain = true order by createTime desc limit :LIMIT";
			sqlQuery = session.createSQLQuery(updateString);
			sqlQuery.setParameter("SUB", subDistributorGuid);
			sqlQuery.setParameter("LIMIT", addCount * -1);
		}

		sqlQuery.executeUpdate();
		return HibernateManager.txCommitFailRollbackClearSession();
	}
	
	public static boolean updateSubDistributorCommSetting(){
		Session session = HibernateManager.getSession();
		Transaction tx = session.beginTransaction();
		try{
			Date now = new Date();
			String selectQuery = "From SubDistributorsModel i where i.modify = true and i.modifyTime <= :NOW";
			Query query = session.createQuery(selectQuery);
			query.setLockOptions(LockOptions.UPGRADE);
			query.setParameter("NOW", now);
			ArrayList<SubDistributorsModel> subDistributorsModels = (ArrayList<SubDistributorsModel>) query.list();
			
			session.clear();
			
			String updateQuery = "UPDATE SubDistributorsModel i "
					+ "SET "
					+ "i.designatedLeveMoneyComm = i.modifyDesignatedLeveMoneyComm, "
					+ "i.levelUpMoneyComm = i.modifyLevelUpMoneyComm , "
					+ "i.modify = false , "
					+ "i.modifyDesignatedLeveMoneyComm = 0, "
					+ "i.modifyLevelUpMoneyComm = 0, "
					+ "i.modifyTime = null "
					+ "Where i.modify = true and i.modifyTime <= :NOW";
			
			query = session.createQuery(updateQuery);
			query.setParameter("NOW", now);
			query.executeUpdate();
			tx.commit();
		}catch(Exception e){
			tx.rollback();
			return false;
		}

		return true;
	}
	
	/**
	 * 用playerId 跟 identifyType 取得使用過的經銷商邀請碼數量
	 * @param guid
	 * @param identifyType
	 * @return
	 */
	public static int getUsedDistributorInviteCodeNum(String guid, byte identifyType) {
		if (identifyType == PlayerIdentityType.BOTH_DISTRIBUTOR_DOWNLINE) {
			identifyType = PlayerIdentityType.DISTRIBUTOR;
		}

		String queryString = "";
		Long count = 0l;
		if (identifyType == PlayerIdentityType.DISTRIBUTOR) {
			queryString += "Select count(s) From DistributorInviteCodesModel s Where distributorGuid = :GUID and invalid = false and (used = true or identifyType = :TYPE)";
			Session session = HibernateManager.getSession();
			Query query = session.createQuery(queryString);
			query.setParameter("TYPE", PlayerIdentityType.SUB_DISTRIBUTOR);
			query.setParameter("GUID", guid);
			count = (Long) query.uniqueResult();
			HibernateManager.endSession();
		
		} else if (identifyType == PlayerIdentityType.SUB_DISTRIBUTOR) {
			queryString += "Select count(s) From DistributorInviteCodesModel s Where  subDistributorGuid = :GUID and invalid = false and identifyType = :TYPE and used = true";
			Session session = HibernateManager.getSession();
			Query query = session.createQuery(queryString);
			query.setParameter("TYPE", identifyType);
			query.setParameter("GUID", guid);
			count = (Long) query.uniqueResult();
			HibernateManager.endSession();
		} else {
			
		}

		
		return count.intValue();
	}

	/**
	 * 取得GUID 跟 使用過經銷商邀請碼次數的map
	 * @param subDistributorGuid
	 * @return
	 */
	public static HashMap<String, Integer> getSubDistributorGuidUsedInviteCodeCountMap(List<String> guids){
		
		if (guids.size() <= 0) {
			return new HashMap<String, Integer>();
		}
		
		String queryString = "Select new map(subDistributorGuid as guid ,count(i) as count) From DistributorInviteCodesModel i where subDistributorGuid in (:GUID) and identifyType = 2 and used = true  group by subDistributorGuid ";
		Session session = HibernateManager.getSession();
		Query query = session.createQuery(queryString);
		query.setParameterList("GUID", guids);
		ArrayList<HashMap<String, Object>> result = (ArrayList<HashMap<String, Object>>)query.list();
		HibernateManager.endSession();
		
		HashMap<String, Integer> resultMap = new HashMap<String, Integer>();
		for (HashMap<String, Object> hashMap : result) {
			resultMap.put((String)hashMap.get("guid"), ((Long)hashMap.get("count")).intValue());
		}
		return resultMap;
	}
	
	/**
	 * 增加邀請碼給經銷商
	 * 
	 * note : 
	 * 從PreparedDistributorInviteCodeModel 搬指定數量過去
	 * 
	 * @param distributorGuid
	 * @param count
	 * @return
	 */
	public static boolean addInviteCodesToDistribuor(String distributorGuid, int count){
		Session session = HibernateManager.getSession();
		if (!addInviteCodesToDistribuor(session, distributorGuid, count)) {
			return false;
		}
		HibernateManager.endSession();
		return true;
	}
	
	private static boolean addInviteCodesToDistribuor(Session session,  String distributorGuid, int count) {
		Transaction tx = session.beginTransaction();
		try {
			Query query = session.createQuery("From PreparedDistributorInviteCodeModel order by id desc desc");
			query.setLockOptions(LockOptions.UPGRADE);
			query.setMaxResults(count);
			ArrayList<PreparedDistributorInviteCodeModel> inviteCodes = (ArrayList<PreparedDistributorInviteCodeModel>) query.list();
			session.clear();
			
			ArrayList<Integer> ids = new ArrayList<Integer>();
			for (PreparedDistributorInviteCodeModel model : inviteCodes) {
				ids.add(model.getId());
			}
			
			query = session.createQuery("Delete From PreparedDistributorInviteCodeModel where id in (:IDS)");
			query.setParameterList("IDS", ids);
			query.executeUpdate();
			session.clear();
			
			tx.commit();
			
			//算一下目前總共的筆數
			query = session.createQuery("Select inviteCodeSn from DistributorInviteCodesModel d where distributorGuid = :GUID order by inviteCodeSn desc");
			query.setParameter("GUID", distributorGuid);
			query.setMaxResults(1);
			Integer total = (Integer) query.uniqueResult() ;
			if(total == null){
				total = 0;
			}
			session.clear();
			
			tx = session.beginTransaction();
			
			DistributorInviteCodesModel model = null;
			for (int i = 0; i < count ; i++) {
				model = new DistributorInviteCodesModel();
				model.setDistributorGuid(distributorGuid);
				model.setInviteCodeSn(total + i + 1);
				model.setInviteCode(inviteCodes.get(i).getInviteCode());
				model.setIdentifyType(PlayerIdentityType.DISTRIBUTOR);
				model.setInvalid(false);
				session.save(model);
				session.flush();
				Thread.sleep(1);
			}
			
			tx.commit();

		}catch (Exception e){
			tx.rollback();
			return false;
		}
		return true;
	}
	
	public static boolean deductDistribuorInviteCode (String distributorGuid, int count) {
		Session session = HibernateManager.getSession();
		Transaction tx = session.beginTransaction();
		
		try{
			Query query = session.createQuery("Select inviteCode From DistributorInviteCodesModel Where distributorGuid = :GUID and used = false and invalid = false order by createTime desc");
			query.setLockOptions(LockOptions.UPGRADE);
			query.setParameter("GUID", distributorGuid);
			query.setMaxResults(count);
			ArrayList<String>  distributorInviteCodes = (ArrayList<String>) query.list();
			if(distributorInviteCodes.size() < count) {
				tx.commit();
				//不夠扣囉
				return false;
			}
			
			session.clear();
			query = session.createQuery("Update DistributorInviteCodesModel i set i.invalid = true , i.invalidTime = :TIME Where inviteCode in (:CODES)");
			query.setParameter("TIME", new Date());
			query.setParameterList("CODES", distributorInviteCodes);
			query.executeUpdate();
			tx.commit();
		} catch(Exception e){
			tx.rollback();
			HibernateManager.endSession();
			return false;
		}
		
		HibernateManager.endSession();
		return true;
	}
}